﻿Imports System.Data.SqlClient
Imports Control_Vuelos_BE

Public Class C_Manifiesto_AdjuntoADO
    ReadOnly MiConexion As New ConexionADO()
    Private cmd As New SqlCommand
    Private dr As SqlDataReader

    Public Function pr_AgregarAdjuntoViaje(ByVal p_CMAdjunto As C_Manifiesto_Adjunto) As Boolean
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            Dim oTran As SqlTransaction = cn.BeginTransaction
            Const sql As String = " INSERT INTO dbo.C_Manifiesto_Adjunto(CodViaje,CodAdjunto,NomAdjunto,FileAdjunto,FchAdjunto,SizAdjunto) VALUES (@CodViaje,@CodAdjunto,@NomAdjunto,@FileAdjunto,@FchAdjunto,@SizAdjunto)"
            Try
                cmd = New SqlCommand(sql, cn)
                cmd.Transaction = oTran
                cmd.Parameters.AddWithValue("@CodViaje", p_CMAdjunto.CodViaje)
                cmd.Parameters.AddWithValue("@CodAdjunto", p_CMAdjunto.CodAdjunto)
                cmd.Parameters.AddWithValue("@NomAdjunto", p_CMAdjunto.NomAdjunto)
                cmd.Parameters.AddWithValue("@FileAdjunto", p_CMAdjunto.FileAdjunto)
                cmd.Parameters.AddWithValue("@FchAdjunto", p_CMAdjunto.FchAdjunto)
                cmd.Parameters.AddWithValue("@SizAdjunto", p_CMAdjunto.SizAdjunto)
                cmd.ExecuteNonQuery()
                oTran.Commit()
                Return True
            Catch myException As Exception
                MsgBox("C_Manifiesto_AdjuntoADO (pr_AgregarAdjuntoViaje) --> " & myException.Message, MsgBoxStyle.Critical)
                oTran.Rollback()
                Return False
            End Try
        End Using
    End Function

    Public Function pr_EliminarAdjunto(ByVal p_CodViaje As String, ByVal p_CodAdjunto As String) As Boolean
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            Dim oTran As SqlTransaction = cn.BeginTransaction
            Const sql As String = "DELETE FROM dbo.C_Manifiesto_Adjunto WHERE CodViaje = @CodViaje AND CodAdjunto = @CodAdjunto "
            Try
                cmd = New SqlCommand(sql, cn)
                cmd.Transaction = oTran
                cmd.Parameters.AddWithValue("@CodViaje", p_CodViaje)
                cmd.Parameters.AddWithValue("@CodAdjunto", p_CodAdjunto)
                cmd.ExecuteNonQuery()
                oTran.Commit()
                Return True
            Catch ex As Exception
                MsgBox("C_Manifiesto_AdjuntoADO (pr_EliminarAdjunto) --> " & ex.Message, MsgBoxStyle.Critical)
                oTran.Rollback()
                Return False
            End Try
        End Using

    End Function

    Public Function pr_ListarManifiestoAdjunto(ByVal vCodman As String) As DataTable
        Using dts As New DataSet()
            Dim ada As SqlDataAdapter
            Try
                Dim cn As New SqlConnection(MiConexion.GetCnx)
                cmd.Connection = cn
                cmd.CommandType = CommandType.StoredProcedure
                cmd.CommandText = "sp_FileManifiestoAdjunto"
                cmd.Parameters.Clear()
                cmd.Parameters.Add(New SqlParameter("@pCodViaje", SqlDbType.Char, 12))
                cmd.Parameters("@pCodViaje").Value = vCodman
                ada = New SqlDataAdapter(cmd)
                ada.Fill(dts, "C_Adjunto")
            Catch ex As Exception
                Throw New Exception(ex.Message)
            End Try
            Return dts.Tables("C_Adjunto")
        End Using
    End Function

    Public Function pr_MostrarAdjunto(ByVal p_CodViaje As String, ByVal p_CodAdjunto As String) As C_Manifiesto_Adjunto
        Try
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Dim _CMAdjunto As New C_Manifiesto_Adjunto
            Using cn
                cn.Open()
                Dim bytes() As Byte
                bytes = Nothing
                cmd = New SqlCommand
                cmd.Connection = cn
                cmd.CommandText = "pr_MostrarAdjunto"
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Clear()
                cmd.Parameters.AddWithValue("@pCodViaje", p_CodViaje)
                cmd.Parameters.AddWithValue("@pCodAdjunto", p_CodAdjunto)
                dr = cmd.ExecuteReader()
                While dr.Read
                    bytes = CType(dr.GetValue(dr.GetOrdinal("FileAdjunto")), Byte())
                    _CMAdjunto.CodViaje = dr.GetValue(dr.GetOrdinal("CodViaje"))
                    _CMAdjunto.CodAdjunto = dr.GetValue(dr.GetOrdinal("CodAdjunto"))
                    _CMAdjunto.NomAdjunto = dr.GetValue(dr.GetOrdinal("NomAdjunto"))
                    _CMAdjunto.FchAdjunto = dr.GetValue(dr.GetOrdinal("FchAdjunto"))
                    _CMAdjunto.SizAdjunto = dr.GetValue(dr.GetOrdinal("SizAdjunto"))
                    _CMAdjunto.FileAdjunto = bytes
                End While
                dr.Close()
                cmd.Dispose()
                Return _CMAdjunto
            End Using
        Catch ex As Exception
            dr.Close()
            cmd.Dispose()
            Throw ex
        End Try
    End Function

    Public Function pr_NumeroSiguiente(ByVal p_CodViaje As String) As Integer
        Try
            Dim nCod As Integer = 0
            Const sql As String = "SELECT ISNULL(MAX(cast(CodAdjunto as int )),0)+1 as nrosgte FROM C_Manifiesto_Adjunto WHERE CodViaje = @CodViaje "
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Using cn
                cn.Open()
                Try
                    cmd = New SqlCommand(sql, cn)
                    cmd.Parameters.AddWithValue("@CodViaje", p_CodViaje)
                    nCod = cmd.ExecuteScalar()
                    Return nCod
                Catch myException As Exception
                    Return 0
                End Try
            End Using
        Catch myException As Exception
            MsgBox("C_Manifiesto_AdjuntoADO (fn_NumeroSiguiente) --> " & myException.Message, MsgBoxStyle.Critical)
            Return 0
        End Try
    End Function

End Class
